package data;

/* Date Created: 19-10-2007
 * Author: D. Advani (#5704790)
 * Description: Class that demonstrate JDBC connection to database
 */
import java.sql.*;
//import javax.sql.DataSource;
//import org.apache.derby.jdbc.EmbeddedDataSource;
import java.io.*;
/**
 * This class creates a connection to a Derby database.
 * Current issues are face with the Embedded driver,
 * Also the Data Source approach causes system crashing.
 */
public class TestApp {

	private static String dbURL = "jdbc:derby:Darwin;create=true";
	private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
   // jdbc Connection
   private static Connection conn = null;
   private static Statement stmt = null;
   private static ResultSet rs =  null;
   private static ResultSetMetaData rsmd = null;
   
	    private static void createConnection()
	    {
	        try
	        {	//using embedded driver
	            Class.forName(driver).newInstance();
	            //Get a connection
	            conn = DriverManager.getConnection(dbURL); 
	        }
	        catch (Exception except)
	        {
	            except.printStackTrace();
	        }
	    }
	/**
	 * Execute a query (SELECT Statement) and display the result to the screen.
	 */
	public void executeQuery(String sql) throws Exception {
		// Create a statement, and run query
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t");  
            }
            
		} catch(SQLException e) {
			System.out.println("Error creating or running statement: ");
			try {
				conn.close();
			} catch(Exception exc) {}
			throw e;
		}

		System.out.println("\n---------------------------------------------------------------------------------");
		// Read the results
		try	{
			String student_id, student_firstname, student_surname, student_add, student_contact, student_status;

			while(rs.next()) {
				student_id = rs.getString("student_id");
				student_firstname = rs.getString("student_firstname");
				student_surname = rs.getString("student_surname");
				student_add = rs.getString("student_add");
				student_contact = rs.getString("student_contact");
				student_status = rs.getString("student_status");
				System.out.println(student_id + "\t\t" + student_firstname + "\t\t" + student_surname + "\t\t" + student_add + "\t\t" + student_contact + "\t\t" + student_status);
			}

			rs.close();
		} catch(Exception e) {
			System.out.println("Error processing results: ");
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch(Exception ex) {}
			throw e;
		}
	}

	/**
	 * Executes various update queries to the database.
	 */
	public void executeUpdate(String sql) throws SQLException {
		// Create a statement and run a query
		try {
			stmt = conn.createStatement();
			int affectedRow = stmt.executeUpdate(sql);
			System.out.println(affectedRow + " row inserted/updated/deleted");
		} catch(SQLException e) {
			System.out.println("Error creating or running statement: ");
			try {
				conn.close();
			} catch(Exception exc) {}
			throw e;
		}
	}
	
	private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }           
        }
        catch (SQLException sqlExcept)
        {
            
        }
	
    }

    public static void main(String[] args)
    {
		TestApp Jdbctest = new TestApp(); // NOPMD by Dan_dell on 10/19/07 2:50 PM

		try {
			//STEP 2: Create connection to database using Data Source
			createConnection();

			//STEP 3 & 4: Create statement, run query and read result
			
			if (args.length==0 || args[0].equals("list")) {
				Jdbctest.executeQuery("SELECT * FROM Student ORDER BY student_id");

			} else if (args[0].equals("insert")) {
				String userSQL = "INSERT INTO STUDENT(student_id, student_firstname, student_surname, student_contact, student_add, student_status) VALUES ('%1','%2','%3','%4','%5','%6')"; // NOPMD by Dan_dell on 10/19/07 2:50 PM
				userSQL = userSQL.replace("%1",readUserInput("Enter student ID: "));
				userSQL = userSQL.replace("%2",readUserInput("Enter student firstname: "));
				userSQL = userSQL.replace("%3",readUserInput("Enter stdietnt surname: "));
				userSQL = userSQL.replace("%4",readUserInput("Enter student contact: "));
				userSQL = userSQL.replace("%5",readUserInput("Enter student adress: "));
				userSQL = userSQL.replace("%3",readUserInput("Enter student status "));
				Jdbctest.executeUpdate(userSQL);

			} else if (args[0].equals("update")) {
				String userSQL = "UPDATE student SET student_firstname='%1', student_add='%2' WHERE student_id='%3'"; // NOPMD by Dan_dell on 10/19/07 2:49 PM
				userSQL = userSQL.replace("%3",readUserInput("Enter the student ID to be updated: "));
				userSQL = userSQL.replace("%1",readUserInput("Enter student's new firstname: "));
				userSQL = userSQL.replace("%2",readUserInput("Enter New student address: "));
				Jdbctest.executeUpdate(userSQL);

			} else if (args[0].equals("delete")) {
				String userSQL = "DELETE FROM student WHERE student_id='%3'"; // NOPMD by Dan_dell on 10/19/07 2:49 PM
				userSQL = userSQL.replace("%3",readUserInput("Enter student's ID to be deleted: "));
				Jdbctest.executeUpdate(userSQL);

			} else if (args[0].equals("find")) {
				String userSQL = "SELECT * FROM student WHERE student_firstname LIKE '%$1%'";
				userSQL = userSQL.replace("$1",readUserInput("Enter student's firstname keyword for search: "));
				Jdbctest.executeQuery(userSQL);
			} else {
				System.out.println("Possible arguments: 'list','insert','update','delete','find'");
			}

			//Shutting down connection
			shutdown();
			//jdbcTest.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
    }


	private static String readUserInput(String message) {
		try {
			BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
			System.out.println(message);
			return reader.readLine().replace("'","''");
		} catch(IOException e) {
			System.out.println("Error reading user input: " + e.toString());
			return "";
		}
	}
}
